USE [WarehouseManagement]
GO
/****** Object:  StoredProcedure [dbo].[InsertProduct]    Script Date: 04/21/2011 10:06:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		duylv00360
-- Create date: 3/14/2011
-- Description:	This stored procedure use to update warehousematerial 
-- =============================================
ALTER PROCEDURE [dbo].[DO_UpdateWarehouseMaterial] 
	-- Add the parameters for the stored procedure here
	@IsMaterial int = 0,
	@IsImport int = 0,	
	@ListMaterialString NVARCHAR(MAX) = NULL	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
    -- Insert statements for procedure here
	IF @IsMaterial = 1
		BEGIN
			IF @IsImport = 1
				BEGIN
					
					DECLARE @XmlPointer1 INT
				 
					EXEC sp_xml_preparedocument @XmlPointer1 OUTPUT, @ListMaterialString

					UPDATE  WarehouseMaterial
					SET		WarehouseMaterial.AveragePricePerUnit = ((WarehouseMaterial.AveragePricePerUnit * WarehouseMaterial.Quantity) - Data.Quantity * Data.PricePerUnit) / (WarehouseMaterial.Quantity - Data.Quantity),
							WarehouseMaterial.Quantity = (WarehouseMaterial.Quantity - Data.Quantity)							
					
					FROM OpenXml(@XmlPointer1, 'Data/Item',2)
					WITH (MaterialID INT, WarehouseID INT, Quantity numeric(10,0), PricePerUnit numeric(10,0)) AS Data	
					WHERE	WarehouseMaterial.MaterialID = Data.MaterialID
							AND
							WarehouseMaterial.WarehouseID = Data.WarehouseID
					EXEC sp_xml_removedocument @XmlPointer1
				END
			ELSE
				BEGIN
					DECLARE @XmlPointer2 INT
				 
					EXEC sp_xml_preparedocument @XmlPointer2 OUTPUT, @ListMaterialString

					UPDATE  WarehouseMaterial
					SET		
							--WarehouseMaterial.AveragePricePerUnit = ((WarehouseMaterial.AveragePricePerUnit * WarehouseMaterial.Quantity) + Data.Quantity * Data.PricePerUnit) / (WarehouseMaterial.Quantity + Data.Quantity),
							WarehouseMaterial.Quantity = (WarehouseMaterial.Quantity + Data.Quantity)							
					
					FROM OpenXml(@XmlPointer2, 'Data/Item',2)
					WITH (MaterialID INT, WarehouseID INT, Quantity numeric(10,0), PricePerUnit numeric(10,0)) AS Data	
					WHERE	WarehouseMaterial.MaterialID = Data.MaterialID
							AND
							WarehouseMaterial.WarehouseID = Data.WarehouseID
					EXEC sp_xml_removedocument @XmlPointer2
				END
				
		END
	ELSE
		BEGIN
			IF @IsImport = 1
				BEGIN
					DECLARE @XmlPointer3 INT
				 
					EXEC sp_xml_preparedocument @XmlPointer3 OUTPUT, @ListMaterialString

					UPDATE  WarehouseProduct
					SET		
							WarehouseProduct.AveragePricePerUnit = ((WarehouseProduct.AveragePricePerUnit * WarehouseProduct.Quantity) - Data.Quantity * Data.PricePerUnit) / (WarehouseProduct.Quantity - Data.Quantity),
							WarehouseProduct.Quantity = (WarehouseProduct.Quantity - Data.Quantity)							
					
					FROM OpenXml(@XmlPointer3, 'Data/Item',2)
					WITH (MaterialID INT, WarehouseID INT, Quantity numeric(10,0), PricePerUnit numeric(10,0)) AS Data	
					WHERE	WarehouseProduct.ProductID = Data.MaterialID
							AND
							WarehouseProduct.WarehouseID = Data.WarehouseID
					EXEC sp_xml_removedocument @XmlPointer3
				END
			ELSE
				BEGIN
					DECLARE @XmlPointer4 INT
				 
					EXEC sp_xml_preparedocument @XmlPointer4 OUTPUT, @ListMaterialString

					UPDATE  WarehouseProduct
					SET		
							--WarehouseProduct.AveragePricePerUnit = ((WarehouseProduct.AveragePricePerUnit * WarehouseProduct.Quantity) + Data.Quantity * Data.PricePerUnit) / (WarehouseProduct.Quantity + Data.Quantity),
							WarehouseProduct.Quantity = (WarehouseProduct.Quantity + Data.Quantity)							
					
					FROM OpenXml(@XmlPointer4, 'Data/Item',2)
					WITH (MaterialID INT, WarehouseID INT, Quantity numeric(10,0), PricePerUnit numeric(10,0)) AS Data	
					WHERE	WarehouseProduct.ProductID = Data.MaterialID
							AND
							WarehouseProduct.WarehouseID = Data.WarehouseID
					EXEC sp_xml_removedocument @XmlPointer4
				END
		END	
	
END


/*
use
WarehouseManagement
go
exec DO_UpdateWarehouseMaterial @IsImport = 1, @IsMaterial = 1, @ListMaterialString = "<Data><Item><MaterialID>1</MaterialID><WarehouseID>1</WarehouseID><Quantity>343</Quantity><PricePerUnit>3434</PricePerUnit></Item></Data>"
*/